﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Utility;
using Portal.Core;
using Portal.Web.Utility;
using Portal.Exam.Model;
using Portal.Systems.Model;


namespace Portal.Exam.Bussiness
{
    public class ExamTemplateBll
    {
        string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }

        public ExamTemplateCls CreateModel(string SiteId, string ExamTemplateId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string Query = " select TableExamTemplate.*,tMaxQuestion=(select sum(NoOfQuestion) from TableExamTemplateConfig where fk_ExamTemplate_Id=ExamTemplateId) from TableExamTemplate where ExamTemplateId = '" + ExamTemplateId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            if (dtResult.Rows.Count == 0)
            {
                return null;
            }

            ExamTemplateCls tExamTemplateCls = new ExamTemplateCls();
            tExamTemplateCls.ExamTemplateId = (string)dtResult.Rows[0]["ExamTemplateId"];
            tExamTemplateCls.ExamTemplateName = (string)dtResult.Rows[0]["ExamTemplateName"];

            try
            {
                tExamTemplateCls.BeforeExamGuidle = (string)dtResult.Rows[0]["BeforeExamGuidle"];
            }
            catch { }
            try
            {
                tExamTemplateCls.ExamTime = (int)dtResult.Rows[0]["ExamTime"];
            }
            catch { }
            try
            {
                tExamTemplateCls.IndexOrder = (int)dtResult.Rows[0]["IndexOrder"];
            }
            catch { }
            try
            {
                tExamTemplateCls.MaxQuestion = (int)dtResult.Rows[0]["tMaxQuestion"];
            }
            catch { }
            try
            {
                tExamTemplateCls.PassPoint = (int)dtResult.Rows[0]["PassPoint"];
            }
            catch { }
            try
            {
                tExamTemplateCls.ActiveExam = (int)dtResult.Rows[0]["ActiveExam"];
            }
            catch { }

            try
            {
                tExamTemplateCls.ExamTemplateType = dtResult.Rows[0]["Category"].ToString();
            }
            catch { }

            return tExamTemplateCls;
        }



        public DataTable ReadingExamTemplate(
            string SiteId,
            string Keyword,
            string Active = "")
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select ExamTemplateId,ExamTemplateName,Category from TableExamTemplate where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "'";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and ExamTemplateName like N'%" + Keyword + "%'";
            }

            if (!string.IsNullOrEmpty(Active))
            {
                Query += " and ActiveExam = '" + Active + "'";
            }

            Query += " order by [IndexOrder]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingExamTemplateForAccountType(
            string SiteId,
            string Keyword,
            string AccountType = ""
            )
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select ExamTemplateId,ExamTemplateName,Category from TableExamTemplate where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and AccountType='" + AccountType + "'";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and ExamTemplateName like N'%" + Keyword + "%'";
            }
            Query += " order by [IndexOrder]";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingActiveExamTemplateForAccountType(string SiteId, string Category)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            DataTable dataTable = new DataTable();
            string Query = "select ExamTemplateId,ExamTemplateName from TableExamTemplate where AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and [ActiveExam]=1 ";
            if (!string.IsNullOrEmpty(Category))
            {
                Query += " and Category='" + Category + "' ";
            }
            Query += " order by [IndexOrder]";
            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingItems(string SiteId, string ExamTemplateId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            DataTable dataTable = new DataTable();
            string Query = "select ExamCollectionId=TableExamCollection.ExamCollectionId, ExamTemplateConfigId,RootExamCollectionName=TableRootExamCollection.ExamCollectionName, ExamCollectionName=TableExamCollection.ExamCollectionName,QuestionType,NoOfQuestion," +
                           "    TQuestion=" +
                           "        (" +
                           "                select count(*) from TableExamCollectionQuestion " +
                           "                    where TableExamCollectionQuestion.frkExamCollectionId=TableExamTemplateConfig.fk_ExamCollection_Id" +
                           "        )," +
                           "        LevelQuestion=IsNull(LevelQuestion,0) from TableExamTemplateConfig " +
                           "            left outer join TableExamCollection on TableExamCollection.ExamCollectionId=fk_ExamCollection_Id" +
                           "            left outer join TableExamCollection as TableRootExamCollection on TableRootExamCollection.ExamCollectionId=TableExamCollection.RootExamCollectionId" +
                //"            left outer join TableGroupQuestion on TableGroupQuestion.GroupQuestionId=fk_GroupQuestion_Id" +
                           "    where fk_ExamTemplate_Id='" + ExamTemplateId + "'";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }


        public DataTable ReadingItemsConfig(string SiteId, string ExamTemplateId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            DataTable dataTable = new DataTable();
            string Query =

                           //"select ExamCollectionId=TableExamCollection.ExamCollectionId, ExamTemplateConfigId,RootExamCollectionName=TableRootExamCollection.ExamCollectionName, ExamCollectionName=TableExamCollection.ExamCollectionName,QuestionType,NoOfQuestion," +
                //"    TQuestion=" +
                //"        (" +
                //"                select count(*) from TableExamCollectionQuestion " +
                //"                    where TableExamCollectionQuestion.frkExamCollectionId=TableExamTemplateConfig.fk_ExamCollection_Id" +
                //"        )," +
                //"        LevelQuestion=IsNull(LevelQuestion,0) from TableExamTemplateConfig " +
                ////"            left outer join TableExamCollection on TableExamCollection.ExamCollectionId=fk_ExamCollection_Id" +
                //"            left outer join TableExamCollection as TableRootExamCollection on TableRootExamCollection.ExamCollectionId=TableExamCollection.RootExamCollectionId" +
                //"            left outer join TableGroupQuestion on TableGroupQuestion.GroupQuestionId=fk_GroupQuestion_Id" +
                //"    where fk_ExamTemplate_Id='" + GroupQuestionId + "'";

                          "select ExamTemplateConfigId,GroupQuestionId = TableGroupQuestion.GroupQuestionId ,GroupQuestionName = TableGroupQuestion.GroupQuestionName,RootGroupQuestionName= TableRootGroupQuestion.GroupQuestionName ,QuestionType,NoOfQuestion, \n" +
                         " LevelQuestion=IsNull(LevelQuestion,0), \n" +
                         "     TQuestion= \n" +
                         "               ( \n" +
                         "                               select count(QuestionId) from TableQuestion,TableExamTemplateConfig  \n" +
                         "                                  where TableQuestion.Type =TableExamTemplateConfig.QuestionType \n" +
                         "                                  and frkGroupQuestionId = TableExamTemplateConfig.fk_GroupQuestion_Id \n" + 
                         "               ) \n" +
                         " from TableExamTemplateConfig,TableGroupQuestion,TableGroupQuestion as TableRootGroupQuestion \n" +
                         " where TableGroupQuestion.GroupQuestionId= [TableExamTemplateConfig].fk_GroupQuestion_Id \n" +
                         " AND \n" +
                         " TableGroupQuestion.RootGroupQuestionId =  TableRootGroupQuestion.GroupQuestionId" +
                         " AND \n" +
                         " fk_Examtemplate_id='" + ExamTemplateId + "' \n";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public string CreateExamTemplate(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string AccountType,
            string ExamTemplateName,
            int ExamTime,
            int MaxQuestion,
            int PassPoint,
            int IndexOrder,
            string BeforeExamGuidle,
            bool IsFreeText)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewExamTemplateId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableExamTemplate(ExamTemplateId,ExamTemplateName,ExamTime,MaxQuestion,PassPoint,IndexOrder,BeforeExamGuidle, AssetCode, Category, AccountType) values (@ExamTemplateId,@ExamTemplateName,@ExamTime,@MaxQuestion,@PassPoint,@IndexOrder,@BeforeExamGuidle, @AssetCode,@IsFreeText,@AccountType)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamTemplateId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamTemplateId"].Value = NewExamTemplateId;

            TempSqlCommand.Parameters.Add("@ExamTemplateName", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamTemplateName"].Value = ExamTemplateName;

            TempSqlCommand.Parameters.Add("@ExamTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@ExamTime"].Value = ExamTime;

            TempSqlCommand.Parameters.Add("@MaxQuestion", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxQuestion"].Value = MaxQuestion;

            TempSqlCommand.Parameters.Add("@PassPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@PassPoint"].Value = PassPoint;

            TempSqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@IndexOrder"].Value = IndexOrder;


            if (BeforeExamGuidle == null) BeforeExamGuidle = "";
            TempSqlCommand.Parameters.Add("@BeforeExamGuidle", SqlDbType.NText);
            TempSqlCommand.Parameters["@BeforeExamGuidle"].Value = BeforeExamGuidle;


            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;

            string QuestionType = string.Empty;
            if (IsFreeText)
            {
                QuestionType = "freetext";
            }

            TempSqlCommand.Parameters.Add("@IsFreeText", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@IsFreeText"].Value = QuestionType;

            TempSqlCommand.Parameters.Add("@AccountType", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AccountType"].Value = AccountType;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewExamTemplateId;
        }



        public void UpdateExamTemplate(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateId,
            string ExamTemplateName,
            int ExamTime,
            int MaxQuestion,
            int PassPoint,
            int IndexOrder,
            string BeforeExamGuidle,
            int ActiveExam,
            bool IsFreeText)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamTemplate set ActiveExam=@ActiveExam, BeforeExamGuidle=@BeforeExamGuidle,ExamTemplateName=@ExamTemplateName,ExamTime=@ExamTime,MaxQuestion=@MaxQuestion,PassPoint=@PassPoint,IndexOrder=@IndexOrder,Category=@Category where ExamTemplateId=@ExamTemplateId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamTemplateId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamTemplateId"].Value = ExamTemplateId;

            TempSqlCommand.Parameters.Add("@ExamTemplateName", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamTemplateName"].Value = ExamTemplateName;

            TempSqlCommand.Parameters.Add("@BeforeExamGuidle", SqlDbType.NText);
            TempSqlCommand.Parameters["@BeforeExamGuidle"].Value = BeforeExamGuidle;


            TempSqlCommand.Parameters.Add("@ExamTime", SqlDbType.Int);
            TempSqlCommand.Parameters["@ExamTime"].Value = ExamTime;

            TempSqlCommand.Parameters.Add("@MaxQuestion", SqlDbType.Int);
            TempSqlCommand.Parameters["@MaxQuestion"].Value = MaxQuestion;

            TempSqlCommand.Parameters.Add("@PassPoint", SqlDbType.Int);
            TempSqlCommand.Parameters["@PassPoint"].Value = PassPoint;

            TempSqlCommand.Parameters.Add("@IndexOrder", SqlDbType.Int);
            TempSqlCommand.Parameters["@IndexOrder"].Value = IndexOrder;

            TempSqlCommand.Parameters.Add("@ActiveExam", SqlDbType.Int);
            TempSqlCommand.Parameters["@ActiveExam"].Value = ActiveExam;

            string QuestionType = string.Empty;
            if (IsFreeText)
            {
                QuestionType = "freetext";
            }

            TempSqlCommand.Parameters.Add("@Category", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@Category"].Value = QuestionType;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void SaveExamTemplateConfig(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateConfigId,
            int NoOfQuestion)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamTemplateConfig set NoOfQuestion=@NoOfQuestion where ExamTemplateConfigId=@ExamTemplateConfigId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamTemplateConfigId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamTemplateConfigId"].Value = ExamTemplateConfigId;

            TempSqlCommand.Parameters.Add("@NoOfQuestion", SqlDbType.Int);
            TempSqlCommand.Parameters["@NoOfQuestion"].Value = NoOfQuestion;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void DeleteExamTemplate(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Delete TableExamTemplateConfig where fk_ExamTemplate_Id=@ExamTemplateId;" +
                           " Delete TableExamTemplate where ExamTemplateId=@ExamTemplateId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamTemplateId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamTemplateId"].Value = ExamTemplateId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void DeleteExamTemplateConfig(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateConfigId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Delete TableExamTemplateConfig where ExamTemplateConfigId=@ExamTemplateConfigId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamTemplateConfigId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamTemplateConfigId"].Value = ExamTemplateConfigId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void AddExamTemplateConfig(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateId,
            string QuestionType,
            string ExamCollectionId,
            int NoOfQuestion,
            int LevelQuestion)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableExamTemplateConfig(ExamTemplateConfigId,fk_ExamTemplate_Id,fk_ExamCollection_Id,QuestionType,NoOfQuestion, LevelQuestion, IndexTemplate) values (@ExamTemplateConfigId,@fk_ExamTemplate_Id,@fk_ExamCollection_Id,@QuestionType,@NoOfQuestion, @LevelQuestion, @IndexTemplate)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamTemplateConfigId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamTemplateConfigId"].Value = System.Guid.NewGuid().ToString();

            TempSqlCommand.Parameters.Add("@fk_ExamTemplate_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_ExamTemplate_Id"].Value = ExamTemplateId;

            //TempSqlCommand.Parameters.Add("@fk_MonThi_Id", SqlDbType.VarChar);
            //TempSqlCommand.Parameters["@fk_MonThi_Id"].Value = MonThiId;

            TempSqlCommand.Parameters.Add("@fk_ExamCollection_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_ExamCollection_Id"].Value = ExamCollectionId;

            TempSqlCommand.Parameters.Add("@QuestionType", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@QuestionType"].Value = QuestionType;

            TempSqlCommand.Parameters.Add("@NoOfQuestion", SqlDbType.Int);
            TempSqlCommand.Parameters["@NoOfQuestion"].Value = NoOfQuestion;

            TempSqlCommand.Parameters.Add("@LevelQuestion", SqlDbType.Int);
            TempSqlCommand.Parameters["@LevelQuestion"].Value = LevelQuestion;

            TempSqlCommand.Parameters.Add("@IndexTemplate", SqlDbType.Int);
            TempSqlCommand.Parameters["@IndexTemplate"].Value = 0;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void AddExamTemplateConfigNew(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateId,
            string QuestionType,
            string GroupQuestionId,
            int NoOfQuestion,
            int LevelQuestion)
        {
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableExamTemplateConfig(ExamTemplateConfigId,fk_ExamTemplate_Id,QuestionType,NoOfQuestion, LevelQuestion, IndexTemplate,fk_GroupQuestion_Id) values (@ExamTemplateConfigId,@fk_ExamTemplate_Id,@QuestionType,@NoOfQuestion, @LevelQuestion, @IndexTemplate,@GroupQuestionId)";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamTemplateConfigId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamTemplateConfigId"].Value = System.Guid.NewGuid().ToString();

            TempSqlCommand.Parameters.Add("@fk_ExamTemplate_Id", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@fk_ExamTemplate_Id"].Value = ExamTemplateId;

            //TempSqlCommand.Parameters.Add("@fk_MonThi_Id", SqlDbType.VarChar);
            //TempSqlCommand.Parameters["@fk_MonThi_Id"].Value = MonThiId;

            //TempSqlCommand.Parameters.Add("@fk_ExamCollection_Id", SqlDbType.VarChar);
            //TempSqlCommand.Parameters["@fk_ExamCollection_Id"].Value = "";

            TempSqlCommand.Parameters.Add("@GroupQuestionId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@GroupQuestionId"].Value = GroupQuestionId;

            TempSqlCommand.Parameters.Add("@QuestionType", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@QuestionType"].Value = QuestionType;

            TempSqlCommand.Parameters.Add("@NoOfQuestion", SqlDbType.Int);
            TempSqlCommand.Parameters["@NoOfQuestion"].Value = NoOfQuestion;

            TempSqlCommand.Parameters.Add("@LevelQuestion", SqlDbType.Int);
            TempSqlCommand.Parameters["@LevelQuestion"].Value = LevelQuestion;

            TempSqlCommand.Parameters.Add("@IndexTemplate", SqlDbType.Int);
            TempSqlCommand.Parameters["@IndexTemplate"].Value = 0;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public DataTable ReadingExamList(
            string SiteId,
            string CourseCode)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            string Query = "select TOP 200 TableExamList.*,ExamTemplateName from TableExamList " +
                             "        left outer join TableExamTemplate on TableExamTemplate.ExamTemplateId=frkExamTemplateId" +
                             " where 1=1 ";
            if (!string.IsNullOrEmpty(CourseCode))
            {
                Query += " and CourseCode='" + CourseCode + "'";
            }

            Query +=
                "     order by CourseCode ASC,ShiftIndex";
            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingStudentInExamList(
            string SiteId,
            string ExamListId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string CommandText = "select *,HasExamSession=(select count(ExamCode) from TableExamPersonalSession  where TableExamPersonalSession.ExamCode=WhoDoExamId and ExamListId='" + ExamListId + "') from TableExamPersonal where frkExamListId='" + ExamListId + "' order by FullName";
            SqlDataAdapter da = new SqlDataAdapter(CommandText, new SqlConnection(GetExamConnectionString(SiteId)));
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingExamConfig(string ConnectionString, string ExamTemplateId)
        {
            DataTable dataTable = new DataTable();
            string Query = "select ExamTemplateConfigId,GroupQuestion=TableGroupQuestion.Name,MonthiId,MonthiName=TableMonthi.Name,QuestionType,NoOfQuestion,TQuestion=(select count(*) from TableQuestion where TableQuestion.fk_Monthi_Id=TableExamTemplateConfig .fk_Monthi_Id),LevelQuestion=IsNull(LevelQuestion,0) from TableExamTemplateConfig " +
                           "        left outer join TableMonthi on TableMonthi.MonthiId=fk_Monthi_Id" +
                           "        left outer join TableGroupQuestion on TableGroupQuestion.GroupQuestionId=fk_GroupQuestion_Id" +
                           "    where fk_ExamTemplate_Id='" + ExamTemplateId + "'";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(ConnectionString));

            da.Fill(dataTable);
            return dataTable;
        }


        public void AutoGenQuestion(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamTemplateId,
            string ExamCode,
            string ExamListId,
            string WhoDoExamId,
            int TotalExamTime)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            string Query = "";
            DataTable dtExamConfig = ReadingExamConfig(GetExamConnectionString(SiteId), ExamTemplateId);
            if (dtExamConfig.Rows.Count == 0) throw new Exception("Chưa thiết lập chi tiết cho template đề thi");
            for (int iIndex = 0; iIndex < dtExamConfig.Rows.Count; iIndex++)
            {
                string MonthiId = (string)dtExamConfig.Rows[iIndex]["MonthiId"];
                string QuestionType = (string)dtExamConfig.Rows[iIndex]["QuestionType"];
                int NoOfQuestion = 0;
                if (dtExamConfig.Rows[iIndex]["NoOfQuestion"] != DBNull.Value)
                {
                    NoOfQuestion = (int)dtExamConfig.Rows[iIndex]["NoOfQuestion"];
                }
                int LevelQuestion = 0;
                if (dtExamConfig.Rows[iIndex]["LevelQuestion"] != DBNull.Value)
                {
                    LevelQuestion = (int)dtExamConfig.Rows[iIndex]["LevelQuestion"];
                }
                Query += "SELECT TOP " + NoOfQuestion + " * FROM TableQuestion where fk_Monthi_Id='" + MonthiId + "' and [Level]=" + LevelQuestion + " and [Type]='" + QuestionType + "' ORDER By NEWID();\r\n";
            }

            DataSet dsQuestion = new DataSet();
            SqlConnection TempSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            TempSqlConnection.Open();
            SqlCommand tSqlCommand = TempSqlConnection.CreateCommand();
            tSqlCommand.CommandText = Query;
            SqlDataAdapter sqlda = new SqlDataAdapter(tSqlCommand);
            sqlda.Fill(dsQuestion);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }


            //int TotalExamTime = 0;
            int TotalQuestion = 0;
            int TotalPoint = 0;
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;

            TempSqlCommand.CommandText =
                " Delete from TableExamAnswer where ExamCode='" + ExamCode + "';" +
                " Delete from TableExamQuestion where ExamCode='" + ExamCode + "'";
            TempSqlCommand.Parameters.Clear();
            TempSqlCommand.ExecuteNonQuery();
            Query = "Insert into TableExamQuestion(ExamQuestionId,ExamCode,Title,Question,Type,MaxPoint,CorrectAnswer,StudentAnswer,MarkedNeedReview,SecondTime,QuestionIndex,AnswerTip,IsMemory,SecondHidden,WhoDoExamId, ExamQuestionLevel,ExamEmbbedHtml, TeacherFlashAnswer) values (@ExamQuestionId,@ExamCode,@Title,@Question,@Type,@MaxPoint,@CorrectAnswer,@StudentAnswer,@MarkedNeedReview,@SecondTime,@QuestionIndex,@AnswerTip,@IsMemory,@SecondHidden,@WhoDoExamId, @ExamQuestionLevel,@ExamEmbbedHtml, @TeacherFlashAnswer);" +
                    "Insert into TableExamAnswer(ExamAnswerId,ExamQuestionId,ExamCode,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer)(select AnswerId,@QuestionId,@ExamCode,AnswerTextFirst,AnswerTextLast,IndexOrder,CorrectAnswer from TableAnswer where fk_Question_Id=@QuestionId)";
            TempSqlCommand.CommandText = Query;

            try
            {
                int QuestionIndex = 0;
                int TotalQuestionInserted = 0;
                for (int iIndex = 0; iIndex < dsQuestion.Tables.Count; iIndex++)
                {
                    for (int iRow = 0; iRow < dsQuestion.Tables[iIndex].Rows.Count; iRow++)
                    {
                        string QuestionId = (string)dsQuestion.Tables[iIndex].Rows[iRow]["QuestionId"];
                        string Title = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Title"];
                        string Question = "";
                        try
                        {
                            Question = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Question"];
                        }
                        catch { }
                        string Type = (string)dsQuestion.Tables[iIndex].Rows[iRow]["Type"];
                        string AnswerTip = "";
                        try
                        {
                            AnswerTip = (string)dsQuestion.Tables[iIndex].Rows[iRow]["AnswerTip"];
                        }
                        catch
                        {
                        }
                        int Level = (int)dsQuestion.Tables[iIndex].Rows[iRow]["Level"];
                        int MaxPoint = (int)dsQuestion.Tables[iIndex].Rows[iRow]["MaxPoint"];
                        TotalPoint = TotalPoint + MaxPoint;
                        TotalQuestion = TotalQuestion + 1;
                        int CorrectAnswer = (int)dsQuestion.Tables[iIndex].Rows[iRow]["CorrectOrder"];
                        int SecondTime = 15;
                        try
                        {
                            if (dsQuestion.Tables[iIndex].Rows[iRow]["SecondTime"] != DBNull.Value)
                            {
                                SecondTime = (int)dsQuestion.Tables[iIndex].Rows[iRow]["SecondTime"];
                            }
                        }
                        catch { }
                        int IsMemory = 0;
                        try
                        {
                            IsMemory = (int)dsQuestion.Tables[iIndex].Rows[iRow]["IsMemory"];
                        }
                        catch { }

                        int SecondHidden = 0;
                        try
                        {
                            SecondHidden = (int)dsQuestion.Tables[iIndex].Rows[iRow]["SecondHidden"];
                        }
                        catch { }

                        byte[] EmbbedHtml = null;
                        if (dsQuestion.Tables[iIndex].Rows[iRow]["EmbbedHtml"] != DBNull.Value)
                        {
                            EmbbedHtml = (byte[])dsQuestion.Tables[iIndex].Rows[iRow]["EmbbedHtml"];
                        }

                        string CorrectAnswerFlash = "";
                        if (dsQuestion.Tables[iIndex].Rows[iRow]["CorrectAnswerFlash"] != DBNull.Value)
                        {
                            CorrectAnswerFlash = (string)dsQuestion.Tables[iIndex].Rows[iRow]["CorrectAnswerFlash"];
                        }
                        TempSqlCommand.Parameters.Clear();
                        TempSqlCommand.Parameters.Add("@ExamQuestionId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@ExamQuestionId"].Value = QuestionId;

                        TempSqlCommand.Parameters.Add("@QuestionId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@QuestionId"].Value = QuestionId;

                        TempSqlCommand.Parameters.Add("@ExamCode", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@ExamCode"].Value = ExamCode;

                        TempSqlCommand.Parameters.Add("@Title", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@Title"].Value = Title;

                        TempSqlCommand.Parameters.Add("@Question", SqlDbType.NText);
                        TempSqlCommand.Parameters["@Question"].Value = Question;

                        TempSqlCommand.Parameters.Add("@Type", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@Type"].Value = Type;

                        TempSqlCommand.Parameters.Add("@MaxPoint", SqlDbType.Int);
                        TempSqlCommand.Parameters["@MaxPoint"].Value = MaxPoint;

                        TempSqlCommand.Parameters.Add("@CorrectAnswer", SqlDbType.Int);
                        TempSqlCommand.Parameters["@CorrectAnswer"].Value = CorrectAnswer;

                        TempSqlCommand.Parameters.Add("@StudentAnswer", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@StudentAnswer"].Value = "";

                        TempSqlCommand.Parameters.Add("@AnswerTip", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@AnswerTip"].Value = AnswerTip;


                        TempSqlCommand.Parameters.Add("@MarkedNeedReview", SqlDbType.Int);
                        TempSqlCommand.Parameters["@MarkedNeedReview"].Value = 0;

                        TempSqlCommand.Parameters.Add("@SecondTime", SqlDbType.Int);
                        TempSqlCommand.Parameters["@SecondTime"].Value = SecondTime;

                        TempSqlCommand.Parameters.Add("@QuestionIndex", SqlDbType.Int);
                        TempSqlCommand.Parameters["@QuestionIndex"].Value = QuestionIndex++;

                        TempSqlCommand.Parameters.Add("@IsMemory", SqlDbType.Int);
                        TempSqlCommand.Parameters["@IsMemory"].Value = IsMemory;

                        TempSqlCommand.Parameters.Add("@SecondHidden", SqlDbType.Int);
                        TempSqlCommand.Parameters["@SecondHidden"].Value = SecondHidden;

                        TempSqlCommand.Parameters.Add("@WhoDoExamId", SqlDbType.VarChar);
                        TempSqlCommand.Parameters["@WhoDoExamId"].Value = WhoDoExamId;


                        TempSqlCommand.Parameters.Add("@ExamQuestionLevel", SqlDbType.Int);
                        TempSqlCommand.Parameters["@ExamQuestionLevel"].Value = Level;

                        TempSqlCommand.Parameters.Add("@ExamEmbbedHtml", SqlDbType.Image);
                        if (EmbbedHtml != null)
                        {
                            TempSqlCommand.Parameters["@ExamEmbbedHtml"].Value = EmbbedHtml;
                        }
                        else
                        {
                            TempSqlCommand.Parameters["@ExamEmbbedHtml"].Value = DBNull.Value;
                        }

                        TempSqlCommand.Parameters.Add("@TeacherFlashAnswer", SqlDbType.NVarChar);
                        TempSqlCommand.Parameters["@TeacherFlashAnswer"].Value = CorrectAnswerFlash.Trim();

                        TotalQuestionInserted++;
                        TempSqlCommand.ExecuteNonQuery();
                    }
                }
                if (TotalQuestionInserted == 0)
                {
                    throw new Exception("Không có câu hỏi nào được thiết lập!");
                }
                TempSqlCommand.CommandText = "Update TableExamPersonalSession set TotalPoint=" + TotalPoint + ",TotalQuestion=" + TotalQuestion + ",DonePoint=0, HasGenQuestion=1,TotalExamTime=" + TotalExamTime + " where ExamCode='" + ExamCode + "'";
                TempSqlCommand.Parameters.Clear();
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void CreateExam(string SiteId, string ExamListId, string ExamTemplateId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            DataTable dtStudent = new ExamTemplateBll().ReadingStudentInExamList(SiteId, ExamListId);
            for (int iIndex = 0; iIndex < dtStudent.Rows.Count; iIndex++)
            {
                string StudentCode = (string)dtStudent.Rows[iIndex]["StudentCode"];
                CreateExamForStudent(SiteId, ExamListId, StudentCode, ExamTemplateId);
            }
        }

        public void CreateExamForStudent(string SiteId, string ExamListId, string StudentCode, string ExamTemplateId)
        {
            int TotalExamTime = 60;
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);


            SqlConnection oSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            oSqlConnection.Open();
            SqlTransaction oSqlTransaction = oSqlConnection.BeginTransaction();
            try
            {
                string Query =
                    " Delete from TableExamAnswer where ExamCode=@WhoDoExamId; " +
                    " Delete from TableExamQuestion where WhoDoExamId=@WhoDoExamId; " +
                    " Delete from TableExamPersonalSession where ExamCode=@ExamCode; " +

                    " Insert into TableExamPersonalSession(ExamCode,ExamApplyPositionId,ExamApplyJobId, ApplyDate, EntryDate, fk_ExamTemplate_Id,WhoDoExamId, TotalExamTime, ExamListId, StudentCode) values (@ExamCode,@ExamApplyPositionId,@ExamApplyJobId, @ApplyDate, @EntryDate, @fk_ExamTemplate_Id,@WhoDoExamId, @TotalExamTime, @ExamListId, @StudentCode); " +
                    " Update TableExamPersonal set CreatedExam=1,ExamTemplateId=@fk_ExamTemplate_Id " +
                    " Update TableExamList set HasGenQuestion=1,frkExamTemplateId=@fk_ExamTemplate_Id ";
                SqlCommand TempSqlCommand = oSqlConnection.CreateCommand();
                TempSqlCommand.Transaction = oSqlTransaction;
                TempSqlCommand.CommandText = Query;

                TempSqlCommand.Parameters.Add("@ExamCode", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@ExamCode"].Value = StudentCode + "$" + ExamListId;

                TempSqlCommand.Parameters.Add("@StudentCode", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@StudentCode"].Value = StudentCode;


                TempSqlCommand.Parameters.Add("@ExamApplyPositionId", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@ExamApplyPositionId"].Value = DBNull.Value;


                TempSqlCommand.Parameters.Add("@ExamApplyJobId", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@ExamApplyJobId"].Value = DBNull.Value;

                TempSqlCommand.Parameters.Add("@ApplyDate", SqlDbType.DateTime);
                TempSqlCommand.Parameters["@ApplyDate"].Value = System.DateTime.Now;

                TempSqlCommand.Parameters.Add("@EntryDate", SqlDbType.DateTime);
                TempSqlCommand.Parameters["@EntryDate"].Value = System.DateTime.Now;

                TempSqlCommand.Parameters.Add("@fk_ExamTemplate_Id", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@fk_ExamTemplate_Id"].Value = ExamTemplateId;

                TempSqlCommand.Parameters.Add("@WhoDoExamId", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@WhoDoExamId"].Value = StudentCode + "$" + ExamListId;

                TempSqlCommand.Parameters.Add("@ExamListId", SqlDbType.VarChar);
                TempSqlCommand.Parameters["@ExamListId"].Value = ExamListId;


                TempSqlCommand.Parameters.Add("@TotalExamTime", SqlDbType.Int);
                TempSqlCommand.Parameters["@TotalExamTime"].Value = TotalExamTime;

                TempSqlCommand.ExecuteNonQuery();

                AutoGenQuestion(oSqlTransaction, SiteId, ExamTemplateId, StudentCode + "$" + ExamListId, ExamListId, StudentCode + "$" + ExamListId, TotalExamTime);
                oSqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                oSqlTransaction.Rollback();
                throw (ex);
            }
        }






        public string CreateExamForUniNetTest(string SiteId, string ExamTemplateId, bool Reset)
        {
            int TotalExamTime = 60;
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            //string ExamTemplateId = "40977a36-3d07-40c7-836a-dc4ad36445d6";
            UserCls OStudentCls = WebSessionUtility.GetCurrentLoginUser();
            string StudentId = OStudentCls.UserId;
            string StudentCode = OStudentCls.LoginName;
            string FullName = OStudentCls.FullName;
            string ExamCode = ExamTemplateId + StudentId;
            SqlConnection oSqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
            oSqlConnection.Open();
            SqlTransaction oSqlTransaction = oSqlConnection.BeginTransaction();
            try
            {
                string CheckQuery = "";
                if (Reset)
                {
                    CheckQuery +=
                        " Delete from TableExamAnswer where ExamCode=@WhoDoExamId; " +
                        " Delete from TableExamQuestion where WhoDoExamId=@WhoDoExamId; " +
                        " Delete from TableExamPersonalSession where ExamCode=@ExamCode; " +
                        " Delete from TableExamPersonal where WhoDoExamId=@WhoDoExamId; ";
                }
                CheckQuery +=
                    "select * from TableExamPersonal where WhoDoExamId=@WhoDoExamId";
                SqlCommand SqlCheckCommand = oSqlConnection.CreateCommand();
                SqlCheckCommand.Transaction = oSqlTransaction;
                SqlCheckCommand.CommandText = CheckQuery;
                SqlCheckCommand.Parameters.Add("@WhoDoExamId", SqlDbType.VarChar).Value = ExamCode;
                SqlCheckCommand.Parameters.Add("@ExamCode", SqlDbType.VarChar).Value = ExamCode;
                SqlDataAdapter sqlda = new SqlDataAdapter(SqlCheckCommand);
                DataTable dtResult = new DataTable();
                sqlda.Fill(dtResult);
                if (dtResult.Rows.Count == 0)
                {

                    string Query =
                        " Insert into TableExamPersonal(WhoDoExamId, FullName, CreatedExam) values (@WhoDoExamId,@FullName,1)" +
                        " Delete from TableExamAnswer where ExamCode=@WhoDoExamId; " +
                        " Delete from TableExamQuestion where WhoDoExamId=@WhoDoExamId; " +
                        " Delete from TableExamPersonalSession where ExamCode=@ExamCode; " +

                        " Insert into TableExamPersonalSession(ExamCode,ExamApplyPositionId,ExamApplyJobId, ApplyDate, EntryDate, fk_ExamTemplate_Id,WhoDoExamId, TotalExamTime, ExamListId, StudentCode) values (@ExamCode,@ExamApplyPositionId,@ExamApplyJobId, @ApplyDate, @EntryDate, @fk_ExamTemplate_Id,@WhoDoExamId, @TotalExamTime, @ExamListId, @StudentCode); " +
                        " Update TableExamPersonal set CreatedExam=1,ExamTemplateId=@fk_ExamTemplate_Id " +
                        " Update TableExamList set HasGenQuestion=1,frkExamTemplateId=@fk_ExamTemplate_Id ";
                    SqlCommand TempSqlCommand = oSqlConnection.CreateCommand();
                    TempSqlCommand.Transaction = oSqlTransaction;
                    TempSqlCommand.CommandText = Query;

                    TempSqlCommand.Parameters.Add("@ExamCode", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@ExamCode"].Value = ExamCode;

                    TempSqlCommand.Parameters.Add("@StudentCode", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@StudentCode"].Value = StudentCode;

                    TempSqlCommand.Parameters.Add("@FullName", SqlDbType.NVarChar);
                    TempSqlCommand.Parameters["@FullName"].Value = FullName;


                    TempSqlCommand.Parameters.Add("@ExamApplyPositionId", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@ExamApplyPositionId"].Value = DBNull.Value;


                    TempSqlCommand.Parameters.Add("@ExamApplyJobId", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@ExamApplyJobId"].Value = DBNull.Value;

                    TempSqlCommand.Parameters.Add("@ApplyDate", SqlDbType.DateTime);
                    TempSqlCommand.Parameters["@ApplyDate"].Value = System.DateTime.Now;

                    TempSqlCommand.Parameters.Add("@EntryDate", SqlDbType.DateTime);
                    TempSqlCommand.Parameters["@EntryDate"].Value = System.DateTime.Now;

                    TempSqlCommand.Parameters.Add("@fk_ExamTemplate_Id", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@fk_ExamTemplate_Id"].Value = ExamTemplateId;

                    TempSqlCommand.Parameters.Add("@WhoDoExamId", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@WhoDoExamId"].Value = ExamCode;

                    TempSqlCommand.Parameters.Add("@ExamListId", SqlDbType.VarChar);
                    TempSqlCommand.Parameters["@ExamListId"].Value = DBNull.Value;


                    TempSqlCommand.Parameters.Add("@TotalExamTime", SqlDbType.Int);
                    TempSqlCommand.Parameters["@TotalExamTime"].Value = TotalExamTime;

                    TempSqlCommand.ExecuteNonQuery();

                    AutoGenQuestion(oSqlTransaction, SiteId, ExamTemplateId, ExamCode, StudentId, StudentId, TotalExamTime);
                }
                oSqlTransaction.Commit();
            }
            catch (Exception ex)
            {
                oSqlTransaction.Rollback();
                throw (ex);
            }
            return ExamCode;
        }
    }
}
